"""
SQLAlchemy 数据库管理类
"""
import os
from typing import Optional, Type, TypeVar, List, Any
from sqlalchemy import create_engine, Engine, text, event
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.pool import QueuePool
from .models import Base, get_db_path

T = TypeVar('T')

class SQLAlchemyDatabaseManager:
    _instance = None
    
    def __init__(self, db_path: str = None):
        """初始化 SQLAlchemy 数据库管理器"""
        if db_path is None:
            db_path = get_db_path()
        self.db_path = db_path
        self.engine: Optional[Engine] = None
        self.Session: Optional[sessionmaker] = None
        self._init_sqlalchemy()
    
    def _configure_connection(self, dbapi_connection, connection_record):
        """配置数据库连接"""
        cursor = dbapi_connection.cursor()
        try:
            # 启用 WAL 模式
            cursor.execute("PRAGMA journal_mode=WAL")
            # 启用外键约束
            cursor.execute("PRAGMA foreign_keys=ON")
            # 设置同步模式为 NORMAL (1)，在大多数情况下提供良好的性能和安全性平衡
            cursor.execute("PRAGMA synchronous=NORMAL")
            # 设置页缓存大小为 4096 (默认是 2000)
            cursor.execute("PRAGMA cache_size=4096")
            # 设置临时存储为内存模式
            cursor.execute("PRAGMA temp_store=MEMORY")
            # 设置锁定模式为 NORMAL，允许并发读取
            cursor.execute("PRAGMA locking_mode=NORMAL")
            # 设置 busy timeout 为 30000 毫秒 (30秒)
            cursor.execute("PRAGMA busy_timeout=30000")
            cursor.close()
        except Exception as e:
            print(f"配置数据库连接时出错: {str(e)}")
            cursor.close()
            raise
    
    def _init_sqlalchemy(self):
        """初始化 SQLAlchemy 引擎和会话工厂"""
        # 使用 sqlite:/// 前缀创建数据库 URL
        database_url = f"sqlite:///{self.db_path}"
        
        # 创建引擎，使用 QueuePool 连接池
        self.engine = create_engine(
            database_url,
            poolclass=QueuePool,  # 使用 QueuePool
            pool_size=5,  # 连接池大小
            max_overflow=10,  # 最大溢出连接数
            pool_timeout=30,  # 连接池超时时间
            pool_recycle=3600,  # 连接回收时间
            connect_args={
                'timeout': 30,  # 连接超时时间
                'check_same_thread': False  # 允许跨线程访问
            }
        )
        
        # 注册连接事件，在每次创建新连接时配置 PRAGMA
        event.listen(self.engine, 'connect', self._configure_connection)
        
        # 创建所有表（如果不存在）
        Base.metadata.create_all(self.engine)
        
        # 创建会话工厂
        self.Session = sessionmaker(bind=self.engine)
    
    def get_session(self) -> Session:
        """获取新的数据库会话"""
        if not self.Session:
            raise RuntimeError("SQLAlchemy 会话工厂未初始化")
        return self.Session()
    
    def execute(self, sql: str, parameters: tuple = None) -> List[Any]:
        """执行原始SQL查询"""
        session = self.get_session()
        try:
            if parameters:
                result = session.execute(text(sql), parameters)
            else:
                result = session.execute(text(sql))
            session.commit()
            return result.fetchall()
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
    
    def add(self, obj: T) -> int:
        """
        添加一个对象到数据库
        :param obj: 要添加的对象
        :return: 新对象的ID
        """
        session = self.get_session()
        try:
            print(f"添加对象到数据库: {obj.__class__.__name__}")
            print(f"对象数据: {obj.__dict__}")
            
            session.add(obj)
            print("对象已添加到会话")
            
            # 刷新会话以获取ID
            session.flush()
            obj_id = obj.id
            print(f"获取到对象ID: {obj_id}")
            
            # 提交事务
            session.commit()
            print("事务已提交")
            
            # 确保对象已保存
            session.refresh(obj)
            print(f"刷新后的对象ID: {obj.id}")
            print(f"刷新后的对象数据: {obj.__dict__}")
            
            return obj_id
            
        except Exception as e:
            session.rollback()
            import traceback
            print(f"添加对象失败: {str(e)}\n{traceback.format_exc()}")
            raise e
        finally:
            session.close()
            print("会话已关闭")

    
    def get_by_id(self, model: Type[T], id: int) -> Optional[T]:
        """通过 ID 获取对象"""
        session = self.get_session()
        try:
            return session.get(model, id)
        finally:
            session.close()
    
    def query(self, model: Type[T], session: Session = None) -> Session:
        """
        获取一个查询对象
        
        Args:
            model: 要查询的模型类
            session: 可选的现有会话，如果提供则使用该会话
            
        Returns:
            Session: 包含查询的会话对象
        """
        if session:
            return session.query(model)
        else:
            # 创建新会话并返回查询对象
            # 注意：调用者需要负责关闭会话
            session = self.get_session()
            return session.query(model)
            
    def query_with_session(self, model: Type[T]) -> tuple[Session, Any]:
        """
        获取一个查询对象和会话，确保会话可以被正确关闭
        
        Args:
            model: 要查询的模型类
            
        Returns:
            tuple: (会话对象, 查询对象)
        """
        session = self.get_session()
        return session, session.query(model)

    def delete_obj(self, obj: T):
        """删除一个对象"""
        session = self.get_session()
        try:
            session.delete(obj)
            session.commit()
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
    
    def update_obj(self, obj: T):
        """更新一个对象"""
        session = self.get_session()
        try:
            session.merge(obj)
            session.commit()
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
    
    def __del__(self):
        """析构函数"""
        if hasattr(self, 'engine') and self.engine:
            self.engine.dispose()
